package com.fs.files.util;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.fs.core.util.CollectionUtil;
import com.fs.core.util.StringUtil;
import com.fs.files.annotation.ExcelField;
import com.fs.files.annotation.ExcelSheet;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.util.*;

/**
 * 读取excel文件 poi 3.17
 * 2017-9-20
 * @author pangps
 */
public class ExcelUtil {

	/**
	 * 判断单元格类型并返回结果
	 * @param cell
	 * @return
	 */
	public static String getCellStringValue(Cell cell, FormulaEvaluator evaluator) {
		String cellValue = "";
		CellType cellType = cell.getCellTypeEnum();
		switch (cellType) {
		case NUMERIC://// 数值类型
			cellValue = String.valueOf(cell.getNumericCellValue());
			break;
		case STRING: // 字符串类型
			cellValue = cell.getStringCellValue();
			break;
		case FORMULA: // 公式
			cellValue = getCellFormulaValue(evaluator.evaluate(cell));//返回公式结果的类型
			break;
		case BLANK:
			break;
		case BOOLEAN:
			break;
		case ERROR:
			break;
		default:
			break;
		}
		return cellValue;
	}

	/**
	 * 判断公式格式的单元格、并返回结果
	 * @param cell
	 * @return
	 */
	private static String getCellFormulaValue(CellValue cell) {
		String cellValue = null;
		CellType cellType = cell.getCellTypeEnum();
		switch (cellType) {
		case STRING:
			cellValue = cell.getStringValue();
			break;
		case NUMERIC:
			cellValue = String.valueOf(cell.getNumberValue());
			break;
		case FORMULA:
			break;
		default:
			break;
		}
		return cellValue;
	}

	/**
	 * 导出Excel对象
	 * @param mapData
	 * @return Workbook
	 */
	private static Workbook exportWorkbook(boolean xlsx, LinkedHashMap<String, List<?>> mapData){
		// data array valid
		if (mapData==null || mapData.size()==0) {
			throw new RuntimeException("data array can not be empty.");
		}
		// book （ XSSFWorkbook=2007/xlsx 、 HSSFWorkbook=2003/xls ）
		Workbook workbook = xlsx?new XSSFWorkbook():new HSSFWorkbook();
		// sheet
		for (Map.Entry<String, List<?>> entry : mapData.entrySet()) {
			if(CollectionUtil.isNotEmpty(entry.getValue())){
				makeSheet(workbook, entry.getKey(), entry.getValue());
			}
		}
		return workbook;
	}

	private static void makeSheet(Workbook workbook,String sheetName, List<?> sheetDataList){
		// data
		if (sheetDataList==null || sheetDataList.size()==0) {
			throw new RuntimeException("data can not be empty.");
		}
		// sheet
		Class<?> sheetClass = sheetDataList.get(0).getClass();
		ExcelSheet excelSheet = sheetClass.getAnnotation(ExcelSheet.class);
		int type = 1;
		if (excelSheet != null) {
			type = excelSheet.type();
		}
//		sheetName = sheetDataList.get(0).getClass().getSimpleName();
//		int type = 1;
//		if (excelSheet != null) {
//			type = excelSheet.type();
//		}
//		Sheet existSheet = workbook.getSheet(sheetName);
//		if (existSheet != null) {
//			for (int i = 2; i <= 1000; i++) {
//				String newSheetName = sheetName.concat(String.valueOf(i));  // avoid sheetName repetition
//				existSheet = workbook.getSheet(newSheetName);
//				if (existSheet == null) {
//					sheetName = newSheetName;
//					break;
//				} else {
//					continue;
//				}
//			}
//		}
		Sheet sheet = workbook.createSheet(sheetName);
		// sheet field
		List<Field> fields = new ArrayList<Field>();
		if (sheetClass.getDeclaredFields()!=null && sheetClass.getDeclaredFields().length>0) {
			for (Field field: sheetClass.getDeclaredFields()) {
				if (Modifier.isStatic(field.getModifiers())) {
					continue;
				}
				fields.add(field);
			}
		}
		if (fields==null || fields.size()==0) {
			throw new RuntimeException("data field can not be empty.");
		}
		CellStyle[] fieldDataStyleArr = new CellStyle[fields.size()];
		int[] fieldWidthArr = new int[fields.size()];
		if(type ==1) {
			// sheet header row
			Row headRow = sheet.createRow(0);
			for (int i = 0; i < fields.size(); i++) {
				// field
				Field field = fields.get(i);
				ExcelField excelField = field.getAnnotation(ExcelField.class);
				String fieldName = field.getName();
				int fieldWidth = 0;
				HorizontalAlignment align = null;
				if (excelField != null) {
					if (excelField.name() != null && excelField.name().trim().length() > 0) {
						fieldName = excelField.name().trim();
					}
					fieldWidth = excelField.width();
					align = excelField.align();
				}
				// field width
				fieldWidthArr[i] = fieldWidth;
				// head-style、field-data-style
				CellStyle fieldDataStyle = workbook.createCellStyle();
				if (align != null) {
					fieldDataStyle.setAlignment(align);
				}
				fieldDataStyleArr[i] = fieldDataStyle;
				CellStyle headStyle = workbook.createCellStyle();
				headStyle.cloneStyleFrom(fieldDataStyle);
//			if (headColorIndex > -1) {
//				headStyle.setFillForegroundColor((short) headColorIndex);
//				headStyle.setFillBackgroundColor((short) headColorIndex);
//				headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//			}
				// head-field data
				Cell cellX = headRow.createCell(i, CellType.STRING);
				cellX.setCellStyle(headStyle);
				cellX.setCellValue(String.valueOf(fieldName));
			}
		}
		if(type ==2){
			// sheet data rows
			for (int dataIndex = 0; dataIndex < sheetDataList.size(); dataIndex++) {
				for (int i = 0; i < fields.size(); i++) {
					Object rowData = sheetDataList.get(dataIndex);
					Row rowX = sheet.createRow(i);
					Field field = fields.get(i);
					try {
						String fieldName = "";
						ExcelField excelField = field.getAnnotation(ExcelField.class);
						if (excelField != null) {
							if (excelField.name() != null && excelField.name().trim().length() > 0) {
								fieldName = excelField.name().trim();
							}
						}
						field.setAccessible(true);
						Object fieldValue = field.get(rowData);
						String fieldValueString = FieldReflectionUtil.formatValue(field, fieldValue);
						Cell cellY0 = rowX.createCell(0, CellType.STRING);
						cellY0.setCellValue(fieldName);
						Cell cellY1 = rowX.createCell(1, CellType.STRING);
						cellY1.setCellValue(fieldValueString);
					} catch (IllegalAccessException e) {
						throw new RuntimeException(e);
					}
				}
			}
		}
		else{
			// sheet data rows
			for (int dataIndex = 0; dataIndex < sheetDataList.size(); dataIndex++) {
				int rowIndex = dataIndex+1;
				Object rowData = sheetDataList.get(dataIndex);
				Row rowX = sheet.createRow(rowIndex);
				for (int i = 0; i < fields.size(); i++) {
					Field field = fields.get(i);
					try {
						field.setAccessible(true);
						Object fieldValue = field.get(rowData);
						String fieldValueString = FieldReflectionUtil.formatValue(field, fieldValue);
						Cell cellX = rowX.createCell(i, CellType.STRING);
						cellX.setCellValue(fieldValueString);
						cellX.setCellStyle(fieldDataStyleArr[i]);
					} catch (IllegalAccessException e) {
						throw new RuntimeException(e);
					}
				}
			}
		}
		// sheet finally
		for (int i = 0; i < fields.size(); i++) {
			int fieldWidth = fieldWidthArr[i];
			if (fieldWidth > 0) {
				sheet.setColumnWidth(i, fieldWidth);
			} else {
				sheet.autoSizeColumn((short)i);
			}
		}
	}

	/**
	 * 导出Excel文件到磁盘
	 * @param xlsx（true： XSSFWorkbook=2007/xlsx；false ：HSSFWorkbook=2003/xls）
	 * @param mapData
	 * @param filePath
	 */
	public static void exportToFile(boolean xlsx, LinkedHashMap<String, List<?>> mapData, String filePath){
		// workbook
		Workbook workbook = exportWorkbook(xlsx, mapData);

		FileOutputStream fileOutputStream = null;
		try {
			// workbook 2 FileOutputStream
			fileOutputStream = new FileOutputStream(filePath);
			workbook.write(fileOutputStream);

			// flush
			fileOutputStream.flush();
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			try {
				if (fileOutputStream!=null) {
					fileOutputStream.close();
				}
			} catch (Exception e) {
				throw new RuntimeException(e);
			}
		}
	}

	/**
	 * 导出Excel字节数据
	 * @param xlsx（true： XSSFWorkbook=2007/xlsx；false ：HSSFWorkbook=2003/xls）
	 * @param mapData
	 * @return byte[]
	 */
	public static byte[] exportToBytes(boolean xlsx, LinkedHashMap<String, List<?>> mapData){
		// workbook
		Workbook workbook = exportWorkbook(xlsx, mapData);
		ByteArrayOutputStream byteArrayOutputStream = null;
		byte[] result = null;
		try {
			// workbook 2 ByteArrayOutputStream
			byteArrayOutputStream = new ByteArrayOutputStream();
			workbook.write(byteArrayOutputStream);
			// flush
			byteArrayOutputStream.flush();
			result = byteArrayOutputStream.toByteArray();
			return result;
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			try {
				if (byteArrayOutputStream != null) {
					byteArrayOutputStream.close();
				}
			} catch (Exception e) {
				throw new RuntimeException(e);
			}
		}
	}

	/**
	 * 该方法用来将Excel中的ABCD列转换成具体的数据
	 * @param column:ABCD列名称
	 * @return integer：将字母列名称转换成数字
	 * **/
	public static int excelColStrToNum(String column) {
		int num = 0;
		int result = 0;
		int length =column.length();
		for(int i = 0; i < length; i++) {
			char ch = column.charAt(length - i - 1);
			num = (int)(ch - 'A' + 1) ;
			num *= Math.pow(26, i);
			result += num;
		}
		return result;
	}

	/**
	 * 该方法用来将具体的数据转换成Excel中的ABCD列
	 * @param columnIndex：需要转换成字母的数字
	 * @return column:ABCD列名称
	 * **/
	public static String excelColIndexToStr(int columnIndex) {
		if (columnIndex <= 0) {
			return null;
		}
		String columnStr = "";
		columnIndex--;
		do {
			if (columnStr.length() > 0) {
				columnIndex--;
			}
			columnStr = ((char) (columnIndex % 26 + (int) 'A')) + columnStr;
			columnIndex = (int) ((columnIndex - columnIndex % 26) / 26);
		} while (columnIndex > 0);
		return columnStr;
	}

	/**
	 * 获取单元格内容
	 * @param cellName
	 * @param objList
	 * @return
	 */
	public static String getCellContent(String cellName, List<List<String>> objList){
		String result = "";
		int index = -1;
		for(int i=0;i<cellName.length();i++) {
			char c=cellName.charAt(i);
			if(c>='0'&&c<='9') {
				index = i;
				break;
			}
		}
		if(index>=0){
			int columnIndex = excelColStrToNum(cellName.substring(0, index))-1;
			int rowIndex = Integer.valueOf(cellName.substring(index))-1;
			int size = objList.size();
			if(size >= rowIndex){
				List<String> list = objList.get(rowIndex);
				if(list.size()>columnIndex){
					result = String.valueOf(list.get(columnIndex));
				}
			}
		}
		return result;
	}

	/**
	 * 导出excel
	 * @param objList 数据
	 * @param mergeMap 单元格合并
	 * @param filePath
	 */
	public static void exportToFile(List<List<String>> objList, LinkedHashMap<String, Object> mergeMap, String filePath){
		Workbook workbook =new XSSFWorkbook();
		Sheet sheet = workbook.createSheet("定值单");
		int rowSize = 0;
		for(List<String> listStr : objList){
			Row headRow = sheet.createRow(rowSize);
			int colSize = 0;
			for(String str : listStr){
				Cell cellX = headRow.createCell(colSize, CellType.STRING);
				cellX.setCellValue(str);
				colSize++;
			}
			rowSize++;
		}
		for(Map.Entry<String, Object> entry : mergeMap.entrySet()) {
			int index = -1;
			String key = entry.getKey();
			for(int i=0;i<key.length();i++) {
				char c=key.charAt(i);
				if(c>='0'&&c<='9') {
					index = i;
					break;
				}
			}
			if(index>=0){
				int firstRow = Integer.valueOf(key.substring(index))-1;
				int firstCol = excelColStrToNum(key.substring(0, index))-1;
				String js = JSONArray.toJSONString(entry.getValue(), SerializerFeature.WriteClassName);
				List<Integer> list = JSONArray.parseArray(js, Integer.class);
				int lastRow = list.get(1)-1+firstRow;
				int lastCol = list.get(0)-1+firstCol;
				//起始行号，终止行号，起始列号，终止列号
				CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
				sheet.addMergedRegion(region);
			}
		}
		FileOutputStream fileOutputStream = null;
		try {
			// workbook 2 FileOutputStream
			fileOutputStream = new FileOutputStream(filePath);
			workbook.write(fileOutputStream);

			// flush
			fileOutputStream.flush();
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			try {
				if (fileOutputStream!=null) {
					fileOutputStream.close();
				}
			} catch (Exception e) {
				throw new RuntimeException(e);
			}
		}
	}


	private static Sheet exportJsonToSheet(Workbook workbook,String jsonData,String sheetName,String[] fileds){
		List<LinkedHashMap<String,String>> allMap = (List<LinkedHashMap<String, String>>) JSONArray.parse(jsonData);
		Sheet sheet = workbook.createSheet(sheetName);
		int rowSize = 0;

		for(Map<String,String> map : allMap){
			Row headRow = sheet.createRow(rowSize);
			boolean red = false;
			int colSize = 0;
			if(rowSize>0 && ("必备性异常告警信息".equals(sheetName) || "ICD校对告警信息".equals(sheetName)) && !(String.valueOf(map.get("checkRuleDesc")).equals("通过") || String.valueOf(map.get("deal")).equals("忽略") )){
				red = true;
			}
			if(rowSize>0 && "预计算告警信息".equals(sheetName) && !String.valueOf(map.get("platResult")).equals(String.valueOf(map.get("settingResult")) )){
				red = true;
			}
			for (String field:fileds) {
				Object value = map.get(field);
				Cell cellX = null;
				if(value instanceof Integer){
					cellX = headRow.createCell(colSize, CellType.NUMERIC);
					cellX.setCellType(CellType.NUMERIC);
					cellX.setCellValue(Double.parseDouble(value.toString()));
				}else{
					cellX = headRow.createCell(colSize, CellType.STRING);
					if(StringUtil.isNotEmpty(value)){
						cellX.setCellValue(String.valueOf(value));
					}else{
						cellX.setCellValue("");
					}
				}

				boolean cellRed = false;
				if( red && ("必备性异常告警信息".equals(sheetName) || "ICD校对告警信息".equals(sheetName)) && ("checkRuleName".equals(field) || "checkRuleDesc".equals(field))){
					cellRed = true;
				}
				if( red && "预计算告警信息".equals(sheetName) && ("platResult".equals(field) || "settingResult".equals(field))){
					cellRed = true;
				}
				if(cellRed){
					CellStyle style = workbook.createCellStyle();
					style.setFillForegroundColor(IndexedColors.RED.getIndex());
					style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
					cellX.setCellStyle(style);
				}

				colSize++;
			}
			rowSize++;
		}
		return sheet;
	}

	private static Sheet exportImageToSheet(Workbook workbook,List<String> imageDataList,String sheetName,List<String> remarkList,String tmpFileDir){
		Sheet sheet = workbook.createSheet(sheetName);
		int row = 0;
		int col = 0;
		for (String imageData: imageDataList) {
			String imageFilePath = tmpFileDir+ UUID.randomUUID() +".png";
			FileUtil.WriteEchartImag2File(imageData,imageFilePath);
			// 先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
			ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
			BufferedImage bufferImg = null;
			File imageFile = null;
			try {
				imageFile = new File(imageFilePath);
				bufferImg = ImageIO.read(imageFile);
				String imghz = imageFilePath.substring(imageFilePath.lastIndexOf(".")+1, imageFilePath.length());
				ImageIO.write(bufferImg, imghz.toUpperCase(), byteArrayOut);
				XSSFClientAnchor anchor = new XSSFClientAnchor();
				anchor.setCol1(col);
				anchor.setCol2(10);
				anchor.setRow1(row);
				row = row+16;
				anchor.setRow2(row);

				int pictureIdx = workbook.addPicture(byteArrayOut.toByteArray(),XSSFWorkbook.PICTURE_TYPE_JPEG);
				// 创建一个工作薄
				Drawing patriarch = sheet.createDrawingPatriarch();
				Picture pic = patriarch.createPicture(anchor, pictureIdx);
				pic.resize(1.0);
				row++;
			} catch (IOException e) {
				e.printStackTrace();
			}finally {
				if(imageFile != null){
					imageFile.delete();
				}
			}

		}
		for (String remark:remarkList) {
			Cell cell = sheet.createRow(row).createCell(0,CellType.STRING);
			cell.setCellValue(remark);
			row++;
		}



		return sheet;
	}


	/**
	 * 导出excel
	 */
	public static void jsonAndImageListToFile(List<String> jsonList,List<String> sheetNames,List<String[]> filedsList,List<String> imageList,
											  List<String> remarkList, String filePath,String tmpFileDir){
		Workbook workbook =new XSSFWorkbook();
		exportImageToSheet(workbook,imageList,sheetNames.get(0),remarkList,tmpFileDir);
		for (int i = 0; i < jsonList.size(); i++) {
			exportJsonToSheet(workbook,jsonList.get(i),sheetNames.get(i+1),filedsList.get(i));
		}
		FileOutputStream fileOutputStream = null;
		try {
			fileOutputStream = new FileOutputStream(filePath);
			workbook.write(fileOutputStream);
			fileOutputStream.flush();
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			try {
				if (fileOutputStream!=null) {
					fileOutputStream.close();
				}
			} catch (Exception e) {
				throw new RuntimeException(e);
			}
		}
	}

}
